package utl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import Dao.Comment;
import Dao.User;

public class Jdbc implements JdbcInterface{	
	static DataSource ds=null;
	static Connection con;
	PreparedStatement pst=null;
	ResultSet rs=null;
	static {
		try {
		Context ctx=new InitialContext();
		ds=(DataSource) ctx.lookup("java:comp/env/jdbc/mysqld");
		con=ds.getConnection();
	} catch (Exception e) {
		e.printStackTrace();
	}
	}
	public void insert(User s) throws SQLException {
		String sql="insert into user(userName,passwd) values (?,?)";
		pst=(PreparedStatement)con.prepareStatement(sql);
		pst.setString(1, s.getName());
		pst.setString(2, s.getPasswd());
		pst.executeUpdate();
	}
	public void delete(String userName) throws SQLException {
		String sql="delete from user where userName=?";
		pst=con.prepareStatement(sql);
		pst.setString(1, userName);
		pst.executeUpdate();
	}
	public void update(String userName,String passwd) throws SQLException {
		String sql="update user set passwd=? where userName=?";
		pst=con.prepareStatement(sql);
		pst.setString(1,passwd);
		pst.setString(2,userName);
		pst.executeUpdate();
	}
	public Set<User> queryAll() throws SQLException {
		String sql="select userName from user";
		Set<User> users =new HashSet<>();
		pst=con.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()) {
			User s = new User();
			s.setName(rs.getString(1));
			users.add(s);
		}
		return users;
	}
	public void close() throws Exception {
		if(rs!=null) {
			rs.close();
		}
		if(pst!=null) {
			pst.close();
		}
	    if(con!=null)
			con.close();
		}
	@Override
	public String query(String name) throws SQLException {
		String sql="select passwd from user where userName=?";
		pst=con.prepareStatement(sql);
		pst.setString(1, name);
		rs=pst.executeQuery();
		User s = new User();
		while(rs.next()) {
			s.setPasswd(rs.getString(1));
		}
		return s.getPasswd() ;
	}
	@Override
	public List<Comment> cqueryAll() throws SQLException {
		String sql="select * from comment";
		List<Comment> comments=new ArrayList<>();
		pst=con.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()) {
			Comment ct=new Comment();
			ct.setUser(rs.getString(1));
			ct.setComment(rs.getString(2));
			comments.add(ct);
		}
		return comments;
	}
	public void cinsert(String name,String comment) throws SQLException {
		String sql="insert into comment values(?,?)";
		pst=con.prepareStatement(sql);
		pst.setString(1, name);
		pst.setString(2, comment);
		pst.executeUpdate();
	}
	public List<Comment> hqueryAll() throws SQLException {
		String sql="select * from hcomment";
		List<Comment> comments=new ArrayList<>();
		pst=con.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()) {
			Comment ct=new Comment();
			ct.setUser(rs.getString(1));
			ct.setComment(rs.getString(2));
			comments.add(ct);
		}
		return comments;
	}
	public void hinsert(String name,String comment) throws SQLException {
		String sql="insert into hcomment values(?,?)";
		pst=con.prepareStatement(sql);
		pst.setString(1, name);
		pst.setString(2, comment);
		pst.executeUpdate();
	}
	public List<Comment> bqueryAll() throws SQLException {
		String sql="select * from bcomment";
		List<Comment> comments=new ArrayList<>();
		pst=con.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()) {
			Comment ct=new Comment();
			ct.setUser(rs.getString(1));
			ct.setComment(rs.getString(2));
			comments.add(ct);
		}
		return comments;
	}
	public void binsert(String name,String comment) throws SQLException {
		String sql="insert into bcomment values(?,?)";
		pst=con.prepareStatement(sql);
		pst.setString(1, name);
		pst.setString(2, comment);
		pst.executeUpdate();
	}
	public List<Comment> squeryAll() throws SQLException {
		String sql="select * from scomment";
		List<Comment> comments=new ArrayList<>();
		pst=con.prepareStatement(sql);
		rs=pst.executeQuery();
		while(rs.next()) {
			Comment ct=new Comment();
			ct.setUser(rs.getString(1));
			ct.setComment(rs.getString(2));
			comments.add(ct);
		}
		return comments;
	}
	public void sinsert(String name,String comment) throws SQLException {
		String sql="insert into scomment values(?,?)";
		pst=con.prepareStatement(sql);
		pst.setString(1, name);
		pst.setString(2, comment);
		pst.executeUpdate();
	}
}
